﻿#include "PublicData.h"
#include <wx/log.h>
sqlite3 *g_db;
wxString g_userid;//用户名
wxString g_username;//用户姓名
int g_userpermit;//用户权限
const wxChar* g_DBFILENAME=_T("householdDB.sqlitedb");

bool open_db(const wxString& filepath, sqlite3 **db)
{
    if( sqlite3_open_v2(filepath.ToUTF8().data(), db, SQLITE_OPEN_FULLMUTEX | SQLITE_OPEN_READWRITE,NULL) != SQLITE_OK ) {
        wxLogDebug(_T("open the database %s error. err_msg=%s"), filepath, sqlite3_errmsg(*db));
        return false;
    }
    return true;
}

void close_db(sqlite3 **db)
{
    if( sqlite3_close(*db)!= SQLITE_OK ) {
        wxLogDebug(_T("close the database error. err_msg=%s"), sqlite3_errmsg(*db));
    } else {
        *db=NULL;
    }
}

//插入一行数据到数据库中:
//fileds:所要插入的“字段－值”序列
//tablename:所要插入的表名
//成功返回true,失败返回false
bool AddIntoDb( const map<wxString,wxString>&fileds,const wxString& tablename )
{
    if(fileds.size()<=0) {
        return false;
    }
    wxString sqlleft=_T("insert into ")+tablename+_T(" (");
    wxString sqlright=_T(" values(");
    auto itnext=fileds.cbegin(),it=fileds.cbegin();
    ++itnext;
    for (; it!=fileds.cend(); ++it,++itnext) {
        if (fileds.cend()==itnext) {
            sqlleft+=it->first+_T(")");
            sqlright+=_T("'")+it->second+_T("')");
        } else {
            sqlleft+=it->first+_T(",");
            sqlright+=_T("'")+it->second+_T("',");
        }
    }
    //写入数据库
    wxString sql=sqlleft+sqlright;
    if(sqlite3_exec(g_db,ToDefaultConv(sql),NULL,NULL,NULL) != SQLITE_OK) {
        wxLogDebug(_T("%s 语法有误. 错误信息:%s"),ToDefaultConv(sql),sqlite3_errmsg(g_db));
        wxLogDebug(_T("%s 语法有误. 错误信息:%s"),sql,sqlite3_errmsg(g_db));
        return false;
    }
    return true;
}

//修改数据:
//fileds:所要修改的“字段－值”序列
//tablename:所要修改的表名
//condition:条件，where语言中的“字段－值”序列,为NULL表明不需要where条件
//成功返回true,失败返回false
bool ModToDb( const map<wxString,wxString>&fileds,const wxString& tablename,const map<wxString,wxString>*condition )
{
    if (fileds.size()<=0) {
        return false;
    }
    wxString sql=_T("update ")+tablename+_T(" set ");
    auto itnext=fileds.cbegin(),it=fileds.cbegin();
    ++itnext;
    for (; it!=fileds.cend(); ++it,++itnext) {
        if (fileds.cend()==itnext) {
            sql+=it->first+_T("='")+it->second+_T("' ");
            break;
        } else {
            sql+=it->first+_T("='")+it->second+_T("',");
        }
    }
    if (condition) {
        sql+=_T(" where ");
        itnext=condition->cbegin();
        ++itnext;
        for (it=condition->cbegin(); it!=condition->cend(); ++it,++itnext) {
            if (condition->cend()==itnext) {
                sql+=it->first+_T("='")+it->second+_T("'");
                break;
            } else {
                sql+=it->first+_T("='")+it->second+_T("' and ");
            }
        }
    }
    //写入数据库
    if(sqlite3_exec(g_db, ToDefaultConv(sql),NULL,NULL,NULL) != SQLITE_OK) {
        wxLogDebug(_T("%s 语法有误. 错误信息:%s"),ToDefaultConv(sql),sqlite3_errmsg(g_db));
        wxLogDebug(_T("%s 语法有误. 错误信息:%s"),sql,sqlite3_errmsg(g_db));
        return false;
    }
    return true;
}

//删除数据:
//tablename:所要删除的表名
//condition:条件，where语言中的“字段－值”序列,为NULL表明不需要where条件(删除整个表的数据)
//成功返回true,失败返回false
bool DelFromDb( const wxString& tablename,const map<wxString,wxString>*condition )
{
    wxString sql=_T("delete from ")+tablename;
    if (condition) {
        sql+=_T(" where ");
        auto it=condition->cbegin(),itnext=condition->cbegin();
        ++itnext;
        for (it=condition->cbegin(); it!=condition->cend(); ++it,++itnext) {
            if (condition->cend()==itnext) {
                sql+=it->first+_T("='")+it->second+_T("'");
            } else {
                sql+=it->first+_T("='")+it->second+_T("' and ");
            }
        }
    }
    //写入数据库
    if(sqlite3_exec(g_db, ToDefaultConv(sql),NULL,NULL,NULL) != SQLITE_OK) {
        wxLogDebug(_T("%s 语法有误. 错误信息:%s"),ToDefaultConv(sql),sqlite3_errmsg(g_db));
        wxLogDebug(_T("%s 语法有误. 错误信息:%s"),sql,sqlite3_errmsg(g_db));
        return false;
    }
    return true;
}

//查询数据表listctrl控件中:
//fileds:所要查询的“数据库字段-字段类型”序列
//tablename:所要查询的表名
//condition:条件，where语言中的“字段－值”序列,为NULL表明不需要where条件
//other:在where后的其他语句，如group by,order by语句
//成功返回true,失败返回false
bool SelFromDb( const vector<pair<wxString,int> >&fileds,const wxString& tablename,
                const map<wxString,wxString>*condition, const wxString& other, wxListCtrl* listctrl )
{
    if (fileds.size()<=0 || listctrl->GetColumnCount()!=fileds.size()) {
        wxLogDebug(_T("fileds size=%d,list ctrl cols=%d"),fileds.size(),listctrl->GetColumnCount());
        return false;
    }
    wxString sql=_T("select ");
    auto vecit=fileds.cbegin(),vecitnext=fileds.cbegin();
    ++vecitnext;
    listctrl->DeleteAllItems();
    size_t col_count=0;
    for (; vecit!=fileds.cend(); ++vecit,++vecitnext) {
        if (fileds.cend()==vecitnext) {
            sql+=vecit->first+_T(" ");
        } else {
            sql+=vecit->first+_T(",");
        }
    }
    sql+=_T(" from ")+tablename;
    if(condition !=NULL) {
        auto it=condition->cbegin(),itnext=condition->cbegin();
        ++itnext;
        if (condition) {
            sql+=_T(" where ");
            for (it=condition->cbegin(); it!=condition->cend(); ++it,++itnext) {
                if (condition->cend()==itnext) {
                    sql+=it->first+_T(" like '%")+it->second+_T("%' ");
                } else {
                    sql+=it->first+_T(" like '%")+it->second+_T("%' and ");
                }
            }
        }
    }
    sql+=_T(" ")+other;
    //显示在列表控件中
    sqlite3_stmt *stmt;
    if(sqlite3_prepare_v2(g_db,ToDefaultConv(sql),LenDefaultConv(sql),&stmt,0)==SQLITE_OK) {
        size_t row_count=0;
        while(sqlite3_step(stmt)==SQLITE_ROW) { //找到记录
            listctrl->InsertItem(row_count,_T(""));
            vecit=fileds.cbegin();
            for (col_count=0; col_count<fileds.size() && vecit !=fileds.cend(); ++col_count,++vecit) {
                wxString itemtext;
                switch(vecit->second) {
                case FILED_INT:
                    itemtext=wxString::Format(_T("%d"),sqlite3_column_int(stmt,col_count));
                    break;
                case FILED_FLOAT:
                    itemtext=wxString::Format(_T("%f"),sqlite3_column_double(stmt,col_count));
                    break;
                case FILED_TEXT:
                    itemtext=wxString((const char*)sqlite3_column_text(stmt,col_count), DefaultConv);
                    break;
                case FILED_DT:
                    itemtext=wxString((const char*)sqlite3_column_text(stmt,col_count), DefaultConv);
                    break;
                case FILED_DATE: {
                    int year,mon,day;
                    sscanf((const char*)sqlite3_column_text(stmt,col_count),"%d-%d-%d",&year,&mon,&day);
                    itemtext=wxString::Format(_T("%d-%d-%d"),year,mon,day);
                }
                break;
                case FILED_TIME: {
                    int hou,minu,sec;
                    sscanf((const char*)sqlite3_column_text(stmt,col_count),"%d-%d-%d",&hou,&minu,&sec);
                    itemtext=wxString::Format(_T("%d-%d-%d"),hou,minu,sec);
                }
                break;
                case FILED_BLOB:
                    break;
                default:
                    break;
                }
                //wxLogDebug(_T("db char=%s,wx char=%s"),(const char*)sqlite3_column_text(stmt,col_count),itemtext);
                listctrl->SetItem(row_count,col_count, itemtext);
            }
            ++row_count;
        }
        if (sqlite3_finalize(stmt) != SQLITE_OK) {
            wxLogDebug(_T("Error on sqlite3_finalize: %s"), sqlite3_errmsg(g_db));
        }
        return true;
    } else {
        wxLogDebug(_T("%s 语法有误. 错误信息:%s"), sql,sqlite3_errmsg(g_db));
        if (sqlite3_finalize(stmt) != SQLITE_OK) {
            wxLogDebug(_T("Error on sqlite3_finalize: %s"), sqlite3_errmsg(g_db));
        }
        return false;
    }
    return true;
}

//查询type表中返回<typename,typeid>序列
//condition为where语句,包含where关键词.为空表明无需要条件判断
const vector<pair<wxString,int> > GetTypeName_ID(const wxString &condition)
{
    vector<pair<wxString,int> >pair_name_id;
    sqlite3_stmt *stmt=NULL;
    wxString sql="select typename,typeid from type "+condition;
    if(sqlite3_prepare_v2(g_db,ToDefaultConv(sql),LenDefaultConv(sql),&stmt,0)==SQLITE_OK) {
        while(sqlite3_step(stmt)==SQLITE_ROW) {
            wxString tmpstr((const char*)sqlite3_column_text(stmt,0),DefaultConv);
            pair_name_id.push_back(std::make_pair(tmpstr,sqlite3_column_int(stmt,1)));
        }
    } else {
        wxLogDebug(_T("%s 语法有误. 错误信息:%s"), sql,sqlite3_errmsg(g_db));
    }
    if (sqlite3_finalize(stmt) != SQLITE_OK) {
        wxLogDebug(_T("Error on sqlite3_finalize: %s"), sqlite3_errmsg(g_db));
    }
    return pair_name_id;
}

//查询user表中返回<username,userid>序列
//condition为where语句,包含where关键词.为空表明无需要条件判断
const vector<pair<wxString,wxString> > GetUserName_ID(const wxString &condition)
{
    vector<pair<wxString,wxString> >pair_name_id;
    sqlite3_stmt *stmt=NULL;
    wxString sql="select username,userid from user "+condition;
    if(sqlite3_prepare_v2(g_db,ToDefaultConv(sql),LenDefaultConv(sql),&stmt,0)==SQLITE_OK) {
        while(sqlite3_step(stmt)==SQLITE_ROW) {
            wxString strname((const char*)sqlite3_column_text(stmt,0),DefaultConv);
            wxString strid((const char*)sqlite3_column_text(stmt,1),DefaultConv);
            pair_name_id.push_back(std::make_pair(strname,strid));
        }
    } else {
        wxLogDebug(_T("%s 语法有误. 错误信息:%s"), sql,sqlite3_errmsg(g_db));
    }
    if (sqlite3_finalize(stmt) != SQLITE_OK) {
        wxLogDebug(_T("Error on sqlite3_finalize: %s"), sqlite3_errmsg(g_db));
    }
    return pair_name_id;
}

//查询menu表中返回<menuname,menuid>序列
//condition为where语句,包含where关键词.为空表明无需要条件判断
const vector<pair<wxString,wxString> > GetMenuName_ID(const wxString &condition )
{
    vector<pair<wxString,wxString> >pair_name_id;
    sqlite3_stmt *stmt=NULL;
    wxString sql="select menuname,menuid from menu "+condition;
    if(sqlite3_prepare_v2(g_db,ToDefaultConv(sql),LenDefaultConv(sql),&stmt,0)==SQLITE_OK) {
        while(sqlite3_step(stmt)==SQLITE_ROW) {
            wxString strname((const char*)sqlite3_column_text(stmt,0),DefaultConv);
            wxString strid((const char*)sqlite3_column_text(stmt,1),DefaultConv);
            pair_name_id.push_back(std::make_pair(strname,strid));
        }
    } else {
        wxLogDebug(_T("%s 语法有误. 错误信息:%s"), sql,sqlite3_errmsg(g_db));
    }
    if (sqlite3_finalize(stmt) != SQLITE_OK) {
        wxLogDebug(_T("Error on sqlite3_finalize: %s"), sqlite3_errmsg(g_db));
    }
    return pair_name_id;
}

//查询名name_***表中的值
const vector<pair<wxString,wxString> > GetFiled_CHS_Name(const wxString& tablename)
{
    vector<pair<wxString,wxString> >filed_CHS_name;
    sqlite3_stmt *stmt=NULL;
    wxString sql=_T("select chsname,dbname from ")+tablename+_T(" where isvisible<>0 order by sequence asc");
    if(sqlite3_prepare_v2(g_db,ToDefaultConv(sql),LenDefaultConv(sql),&stmt,0)==SQLITE_OK) {
        while(sqlite3_step(stmt)==SQLITE_ROW) {
            wxString strname((const char*)sqlite3_column_text(stmt,0),DefaultConv);
            wxString strid((const char*)sqlite3_column_text(stmt,1),DefaultConv);
            filed_CHS_name.push_back(std::make_pair(strname,strid));
        }
    } else {
        wxLogDebug(_T("%s 语法有误. 错误信息:%s\n"), sql,sqlite3_errmsg(g_db));
    }
    if (sqlite3_finalize(stmt) != SQLITE_OK) {
        wxLogDebug(_T("Error on sqlite3_finalize: %s\n"), sqlite3_errmsg(g_db));
    }
    return filed_CHS_name;
}

////////sqlite数据库的备份与恢复
/*
** This function is used to load the contents of a database file on disk
** into the "main" database of open database connection pInMemory, or
** to save the current contents of the database opened by pInMemory into
** a database file on disk. pInMemory is probably an in-memory database,
** but this function will also work fine if it is not.
**
** Parameter zFilename points to a nul-terminated string containing the
** name of the database file on disk to load from or save to. If parameter
** isSave is non-zero, then the contents of the file zFilename are
** overwritten with the contents of the database opened by pInMemory. If
** parameter isSave is zero, then the contents of the database opened by
** pInMemory are replaced by data loaded from the file zFilename.
**
** If the operation is successful, SQLITE_OK is returned. Otherwise, if
** an error occurs, an SQLite error code is returned.
*/
int loadOrSaveDb(sqlite3 *pInMemory, const char *zFilename, int isSave)
{
    int rc;                   /* Function return code */
    sqlite3 *pFile;           /* Database connection opened on zFilename */
    sqlite3_backup *pBackup;  /* Backup object used to copy data */
    sqlite3 *pTo;             /* Database to copy to (pFile or pInMemory) */
    sqlite3 *pFrom;           /* Database to copy from (pFile or pInMemory) */
    /* Open the database file identified by zFilename. Exit early if this fails
    ** for any reason. */
    rc = sqlite3_open(zFilename, &pFile);
    if( rc==SQLITE_OK ) {
        /* If this is a 'load' operation (isSave==0), then data is copied
        ** from the database file just opened to database pInMemory.
        ** Otherwise, if this is a 'save' operation (isSave==1), then data
        ** is copied from pInMemory to pFile.  Set the variables pFrom and
        ** pTo accordingly. */
        pFrom = (isSave ? pInMemory : pFile);
        pTo   = (isSave ? pFile     : pInMemory);

        /* Set up the backup procedure to copy from the "main" database of
        ** connection pFile to the main database of connection pInMemory.
        ** If something goes wrong, pBackup will be set to NULL and an error
        ** code and  message left in connection pTo.
        **
        ** If the backup object is successfully created, call backup_step()
        ** to copy data from pFile to pInMemory. Then call backup_finish()
        ** to release resources associated with the pBackup object.  If an
        ** error occurred, then  an error code and message will be left in
        ** connection pTo. If no error occurred, then the error code belonging
        ** to pTo is set to SQLITE_OK.
        */
        pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
        if( pBackup ) {
            (void)sqlite3_backup_step(pBackup, -1);
            (void)sqlite3_backup_finish(pBackup);
        }
        rc = sqlite3_errcode(pTo);
    }
    /* Close the database connection opened on database file zFilename
    ** and return the result of this function. */
    (void)sqlite3_close(pFile);
    return rc;
}

/*
** Perform an online backup of database pDb to the database file named
** by zFilename. This function copies 5 database pages from pDb to
** zFilename, then unlocks pDb and sleeps for 250 ms, then repeats the
** process until the entire database is backed up.
**
** The third argument passed to this function must be a pointer to a progress
** function. After each set of 5 pages is backed up, the progress function
** is invoked with two integer parameters: the number of pages left to
** copy, and the total number of pages in the source file. This information
** may be used, for example, to update a GUI progress bar.
**
** While this function is running, another thread may use the database pDb, or
** another process may access the underlying database file via a separate
** connection.
**
** If the backup process is successfully completed, SQLITE_OK is returned.
** Otherwise, if an error occurs, an SQLite error code is returned.
*/
int backupDb(
    sqlite3 *pDb,               /* Database to back up */
    const char *zFilename,      /* Name of file to back up to */
    void(*xProgress)(int, int)  /* Progress function to invoke */
)
{
    int rc;                     /* Function return code */
    sqlite3 *pFile;             /* Database connection opened on zFilename */
    sqlite3_backup *pBackup;    /* Backup handle used to copy data */

    /* Open the database file identified by zFilename. */
    rc = sqlite3_open(zFilename, &pFile);
    if( rc==SQLITE_OK ) {
        /* Open the sqlite3_backup object used to accomplish the transfer */
        pBackup = sqlite3_backup_init(pFile, "main", pDb, "main");
        if( pBackup ) {
            /* Each iteration of this loop copies 5 database pages from database
            ** pDb to the backup database. If the return value of backup_step()
            ** indicates that there are still further pages to copy, sleep for
            ** 250 ms before repeating. */
            do {
                rc = sqlite3_backup_step(pBackup, 5);
                if(xProgress) {
                    xProgress( sqlite3_backup_remaining(pBackup),
                               sqlite3_backup_pagecount(pBackup) );
                }
                if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ) {
                    sqlite3_sleep(250);
                }
            } while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED );

            /* Release resources allocated by backup_init(). */
            (void)sqlite3_backup_finish(pBackup);
        }
        rc = sqlite3_errcode(pFile);
    }
    /* Close the database connection opened on database file zFilename
    ** and return the result of this function. */
    (void)sqlite3_close(pFile);
    return rc;
}
